package com.yxuan.springboot.mapper;

import com.yxuan.springboot.model.OneParaRange;
import com.yxuan.springboot.model.OneParagraph;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 
 * @author zchao
 *    OneParagraphMapper层
 * @date 2023-05-17 17:19:09
 */
@Mapper
public interface OneParagraphMapper extends BaseMapper<OneParagraph> {

    @Select("select classification from one_paragraph where province = #{province} and year = #{year} GROUP BY province,year,classification")
    List<OneParagraph> getClassificationGroup(@Param("province") String province,@Param("year") String year);

//    查询历年等位分
//    @Select("SELECT * FROM one_paragraph WHERE \n" +
//            "CONVERT(SUBSTRING_INDEX(peopel_intervals, '-', -1), UNSIGNED) >= (\n" +
//            "\t\tSELECT up_in_people FROM one_paragraph WHERE \n" +
//            "\t\tCONVERT(SUBSTRING_INDEX(score, '-', -1), UNSIGNED) >= #{score} \n" +
//            "\t\t\tand province = #{province}\n" +
//            "\t\t\tand classification = #{classification}\n" +
//            "\t\t\tand year = #{year}\n" +
//            "\t\tORDER BY CONVERT(SUBSTRING_INDEX(score, '-', -1), UNSIGNED) LIMIT 1)\n" +
//            "\tand province = #{province}\n" +
//            "\tand classification = #{classification}\n" +
//            "GROUP BY year\n" +
//            "ORDER BY CONVERT(SUBSTRING_INDEX(peopel_intervals, '-', -1), UNSIGNED);")
//    List<OneParagraph> getHistorySame(@Param("score") String score,@Param("province") String province,@Param("classification") String classification,@Param("year") String year);

    //    查询历年等位分
    List<OneParagraph> getHistorySame(@Param("score") String score,@Param("province") String province,@Param("classification") String classification,@Param("year") String year);


    @Select("SELECT CONCAT(FLOOR((score)/25)*25+1, '-', FLOOR((score)/25)*25+25) AS score_range, SUM(one_in_people) AS total_people FROM ( SELECT id, CASE WHEN LOCATE('-', score) > 0 THEN SUBSTRING(score, 1, LOCATE('-', score)-1) ELSE score END AS score, one_in_people FROM one_paragraph WHERE province = #{province} AND year = #{year} AND classification = #{classification} ) AS t GROUP BY FLOOR((score)/25) ORDER BY score_range ASC")
    List<OneParaRange> getRangeNumber(@Param("province") String province,@Param("year") String year,@Param("classification") String classification);


    List<OneParagraph> getHistorySameOne(@Param("score") String score, @Param("province") String province, @Param("classification") String classification, @Param("year") String year);



}